﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace Universe.Cache
{
    public partial class CacheSqlCeStorage : ICacheStorage
    {
        public const int PrefixLength = 1000;
        public readonly CacheSqlCeConnection CacheSqlCeConnection;

        public CacheSqlCeStorage(CacheSqlCeConnection cacheSqlCeConnection)
        {
            CacheSqlCeConnection = cacheSqlCeConnection;
            cacheSqlCeConnection.Configure();
        }

        Dictionary<string, Guid> _Keys = new Dictionary<string, Guid>(StringComparer.InvariantCultureIgnoreCase);
        private DateTime _KeysExpiration = DateTime.MinValue;

        private Dictionary<string, Guid> GetKeys()
        {
            lock(_Keys)
            {
                var now = DateTime.Now;
                if (now >= _KeysExpiration)
                {
                    _KeysExpiration = now.AddHours(1);
                    _Keys = new Dictionary<string, Guid>(StringComparer.InvariantCultureIgnoreCase);
                }
            }

            return _Keys;
        }

        private Guid GetKeyId(SqlCeConnection con, string key)
        {
            var keys = GetKeys();
            
            // In memory?
            Guid keyId;
            if (keys.TryGetValue(key, out keyId))
            {
                return keyId;
            }

            // already in DB
            // using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS))
            {
                // con.Open();

                string prefix =
                    key.Length > PrefixLength
                        ? key.Substring(0, PrefixLength)
                        : key;

                string suffix =
                    key.Length > PrefixLength
                        ? key.Substring(PrefixLength)
                        : null;

                using (SqlCeCommand cmd = new SqlCeCommand("Select [Id], [Suffix] From [Key] Where [Prefix] = @Prefix", con))
                {
                    cmd.Parameters.Add("@Prefix", SqlDbType.NVarChar).Value = prefix;
                    using (SqlCeDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            keyId = new Guid((byte[])rdr.GetValue(0));
                            string suff = rdr.IsDBNull(1) ? null : rdr.GetString(1);
                            if (suff == suffix)
                            {
                                keys[key] = keyId;
                                return keyId;
                            }
                        }
                    }
                }

                using (SqlCeCommand cmd = new SqlCeCommand("Insert [Key] (Id, Prefix, Suffix) Values(@Id, @Prefix, @Suffix)", con))
                {
                    keyId = Guid.NewGuid();
                    cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                    cmd.Parameters.Add("@Prefix", SqlDbType.NVarChar).Value = prefix;
                    cmd.Parameters.Add("@Suffix", SqlDbType.NText).Value = suffix ?? (object) DBNull.Value;
                    cmd.ExecuteNonQuery();
                    keys[key] = keyId;
                    return keyId;
                }
            }
        }

        public bool Get(string key, byte[] stamp, out byte[] result)
        {

            using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS /* + ";Mode=Read Only" */))
            {
                con.Open();
                Guid keyId = GetKeyId(con, key);

                bool isExists = false;
                using (SqlCeCommand cmd = new SqlCeCommand(SqlGet1, con))
                {
                    cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                    cmd.Parameters.Add("@Now", SqlDbType.DateTime).Value = DateTime.Now;
                    object rawStamp = cmd.ExecuteScalar();
                    if (rawStamp != null && rawStamp != DBNull.Value)
                    {
                        byte[] dbStamp = (byte[]) rawStamp;
                        isExists = Utils.Equals(dbStamp, stamp);
                    }
                }

                if (isExists)
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(SqlGet2, con))
                    {
                        cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                        result = (byte[]) cmd.ExecuteScalar();
                        return true;
                    }
                }

                result = null;
                return false;
            }
        }

        public void Put2(string key, byte[] stamp, DateTime expired, byte[] value)
        {

            using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS))
            {
                con.Open();
                Guid keyId = GetKeyId(con, key);

                bool isExists;
                using (SqlCeCommand cmd = new SqlCeCommand(SqlPut1, con))
                {
                    cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                    object res = cmd.ExecuteScalar();
                    isExists = res != null && res != DBNull.Value;
                }

                if (!isExists)
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(SqlPut2, con))
                    {
                        cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                        cmd.Parameters.Add("@Expired", SqlDbType.DateTime).Value = expired;
                        cmd.Parameters.Add("@Stamp", SqlDbType.Image).Value = stamp;
                        cmd.Parameters.Add("@Value", SqlDbType.Image).Value = value;
                        cmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(SqlPut3, con))
                    {
                        cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                        cmd.Parameters.Add("@Expired", SqlDbType.DateTime).Value = expired;
                        cmd.Parameters.Add("@Stamp", SqlDbType.Image).Value = stamp;
                        cmd.Parameters.Add("@Value", SqlDbType.Image).Value = value;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        public void Put(string key, byte[] stamp, DateTime expired, byte[] value)
        {
            
            using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS))
            {
                con.Open();
                Guid keyId = GetKeyId(con, key);

                using (SqlCeCommand cmd = new SqlCeCommand(SqlPut_B1, con))
                {
                    cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                    cmd.ExecuteNonQuery();
                }

                using (SqlCeCommand cmd = new SqlCeCommand(SqlPut2, con))
                {
                    cmd.Parameters.Add("@Id", SqlDbType.Binary).Value = keyId.ToByteArray();
                    cmd.Parameters.Add("@Expired", SqlDbType.DateTime).Value = expired;
                    cmd.Parameters.Add("@Stamp", SqlDbType.Image).Value = stamp;
                    cmd.Parameters.Add("@Value", SqlDbType.Image).Value = value;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void FlushExpiration()
        {
            using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS))
            {
                con.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(SqlFlush, con))
                {
                    cmd.Parameters.Add("@Now", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public DataTable SelectOnCleaning()
        {
            using (SqlCeConnection con = new SqlCeConnection(CacheSqlCeConnection.CS))
            {
                Stopwatch sw = Stopwatch.StartNew();
                con.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(SqlCleanBySize, con))
                using(SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    DataTable ret = new DataTable("Sizes");
                    da.Fill(ret);
                    var msg = string.Format("Read sizes: {0} msec, count: {1}", sw.ElapsedMilliseconds, ret.Rows.Count);
                    Debug.WriteLine(msg);
                    return ret;
                }
            }
        }

        public void Clean(int maxMegabytes)
        {
            throw new System.NotImplementedException();
        }

        private static readonly string

/*
            SqlGet1 =
                "Select [Stamp] From [Cache] WITH (ROWLOCK, XLOCK) Where [Id]= @Id and [Expired] >= @Now",

            SqlGet2 =
                "Select [Value] From [Cache] WITH (ROWLOCK, XLOCK) Where [Id]= @Id",

            SqlPut1 =
                "Select 1 From [Cache] WITH (ROWLOCK, XLOCK) Where [Id] = @Id",

            SqlPut2 =
                "Insert [Cache] WITH (ROWLOCK, XLOCK) ([Id], [Expired], [Stamp], [Value])  Values(@Id, @Expired, @Stamp, @Value)",

            SqlPut3 =
                "Update [Cache] WITH (ROWLOCK, XLOCK) Set [Expired] = @Expired, [Stamp] = @Stamp, [Value] = @Value Where [Id] = @Id",

            SqlFlush =
                "Delete [Cache] WITH (ROWLOCK, XLOCK) Where Expired < @Now",

            SqlPut_B1 =
                "Delete [Cache] WITH (ROWLOCK, XLOCK) Where [Id] = @Id";
*/

            SqlGet1 =
                "Select [Stamp] From [Cache] Where [Id]= @Id and [Expired] >= @Now",

            SqlGet2 =
                "Select [Value] From [Cache] Where [Id]= @Id",

            SqlPut1 =
                "Select 1 From [Cache] Where [Id] = @Id",

            SqlPut2 =
                "Insert [Cache] ([Id], [Expired], [Stamp], [Value])  Values(@Id, @Expired, @Stamp, @Value)",

            SqlPut3 =
                "Update [Cache] Set [Expired] = @Expired, [Stamp] = @Stamp, [Value] = @Value Where [Id] = @Id",

            SqlFlush =
                "Delete [Cache] Where Expired < @Now",

            SqlPut_B1 =
                "Delete [Cache] Where [Id] = @Id",

            SqlCleanBySize =
                "Select [Id], DataLength([Value]) From [Cache] Order by [Expired] Desc";
            
    }
}
